package com.ocom.human.dao;


import com.ocom.common.entity.human.EntityPersonnelInfo;
import com.ocom.common.request.human.PersonQueryVo;
import com.ocom.common.vo.human.*;
import icu.mhb.mybatisplus.plugln.base.mapper.JoinBaseMapper;
import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;


@Mapper
public interface IDaoPersonnelInfo extends JoinBaseMapper<EntityPersonnelInfo> {

    @Select({"<script>",
            "select p.region_id,p.region_online,p.com_id,p.p_id,p.dept_id,IFNULL(d.dept_name,c.com_name)as dept_name,\n" +
                    "p.p_name,p.p_pwd,p.p_code,p.p_type,p.p_sex,p.p_phone,p.p_identity," +
                    "p_born,p.p_address,p.p_photo_url,p.p_photo_url2,p.status,p.create_time,a.end_date as accountEndDate \n" +
                   
                    "FROM personnel_info p \n" +
                    "LEFT JOIN company_info c ON p.com_id=c.com_id " +
                    "LEFT JOIN dept_info d ON p.com_id=d.com_id AND p.dept_id=d.dept_id \n" +

                    "LEFT JOIN xf_account_info a ON p.com_id=a.com_id and p.p_id=a.p_id \n " +
                    "where p.com_id=#{comId} and p.status=0 " +
                    " <when test=\"pName!=null and  pName !=''\" > " +
                    "  AND p.p_name LIKE concat('%', #{pName}, '%')  " +
                    " </when> " +
                    " <when test='pId != null' > " +
                    " AND p.p_id=#{pId}" +
                    " </when> " +
                    " <when test='deptId != null' > " +
                    " AND p.dept_id=#{deptId}" +
                    " </when> " +
                    " <when test='parentDeptID != null' > " +
                    " AND p.dept_id=#{parentDeptID}" +
                    " </when> " +
                    "<when test=\"pCode != null and pCode !='' \"  > " +
                    " AND p.p_code=#{pCode}" +
                    " </when> " +
                    " <when test='pType != null' > " +
                    " AND p.p_type=#{pType}" +
                    " </when> " +
                    " <when test='pPhone != null' > " +
                    " AND p.p_phone=#{pPhone,typeHandler = com.ocom.common.entity.typehandler.PersonInfoHandle }" +
                    " </when> " +
                    " <when test='pIden != null' > " +
                    " AND p.p_identity=#{pIden,typeHandler = com.ocom.common.entity.typehandler.PersonInfoHandle }" +
                    " </when> " +
                    " order by CONVERT( p.p_name USING gbk ) COLLATE gbk_chinese_ci ASC " +

            "</script>"})
    public List<UserInfoYkmVo> PersonnelGetPersonInfo(Long comId, Integer pId, Long deptId,
                                                      Long parentDeptID, @Param("pName") String pName, String pCode,
                                                      String pType, String pPhone, String pIden);


    @Select({"<script>",
            "select p.com_id,p.p_id,p.dept_id,d.dept_name,\n" +
                    "p.p_name,p.p_code,p.p_type,p.p_sex,p.p_phone,p.p_identity,p.encry_phone,p.encry_identity," +
                    "p_born,p.p_address,p.p_photo_url,p.p_photo_url2,p.status,p.create_time," +
                    "p.bank_no  \n" +
             //       "y.update_time,\n" +
             //       "y.code_color,y.color_time,y.hs_result,y.hs_time,y.ym_num,y.ym_time \n" +
                    "FROM personnel_info p \n" +
                    "LEFT JOIN dept_info d ON p.com_id=d.com_id AND p.dept_id=d.dept_id \n" +
//                    "LEFT JOIN personnel_ykm y ON p.com_id=y.com_id and p.p_id=y.p_id \n " +
                    "where p.com_id=#{comId} and p.status=0 AND p.p_id=#{pId}",
            "</script>"})
    public UserInfoYkmVo getPersonInfoById(Long comId, Long pId);


    @Select({"<script>",
            "select p.com_id,p.p_id,p.dept_id,d.dept_name,p.p_name,p.p_code,p.p_type,p.p_sex,p.p_tags, \n" + //p.bank_no,
//                    "CONCAT(LEFT(p.p_phone,2),'********',RIGHT(p.p_phone,3)) as p_phone,\n" +
//                    "CONCAT(LEFT(p.p_identity,2),'********',RIGHT(p.p_identity,3)) as p_identity,\n" +
//                    "c.card_no ,c.card_state,c.end_date," +
                    "c.card_no ,c.card_state," +
                    "p_born,p.p_address,p.p_photo_url,p.p_photo_url2,p.status,p.create_time,a.end_date FROM personnel_info p\n" +
                    "LEFT JOIN dept_info d ON p.com_id=d.com_id AND p.dept_id=d.dept_id  " +
                    "LEFT JOIN xf_account_info as a ON a.p_id = p.p_id AND a.com_id=p.com_id "+
                    "LEFT JOIN card_info c ON p.com_id=c.com_id AND p.p_id=c.p_id AND card_state IN (0,1)"+
                    "where p.com_id=#{comId} " +
                    " <when test=\"status!=null\" > " +
                    "  AND p.status=#{status}" +
                    " </when> " +
                    " <when test=\"pName!=null and  pName !=''\" > " +
                    "  AND p.p_name LIKE concat('%', #{pName}, '%')  " +
                    " </when> " +
                    " <when test=\"cardNo!=null and  cardNo !=''\" > " +
                    "  AND c.card_no=#{cardNo}" +
                    " </when> " +
                    " <when test=\"bankNo!=null and  bankNo !=''\" > " +
                    "  AND p.bank_no=#{bankNo}" +
                    " </when> " +
                    " <when test='pId != null' > " +
                    " AND p.p_id=#{pId}" +
                    " </when> " +
                    " <when test='deptId != null' > " +
                    " AND p.dept_id=#{deptId}" +
                    " </when> " +
                    " <when test='parentDeptId != null' > " +
                    " AND p.dept_id=#{parentDeptId}" +
                    " </when> " +
                    "<when test=\"pCode != null and pCode !='' \"  > " +
                    " AND p.p_code=#{pCode}" +
                    " </when> " +
                    " <when test='pType != null' > " +
                    " AND p.p_type=#{pType}" +
                    " </when> " +
                    " <when test='pPhone != null' > " +
                    " AND p.p_phone=#{pPhone,typeHandler = com.ocom.common.entity.typehandler.PersonInfoHandle }" +
                    " </when> " +
                    " <when test='pIdentity != null' > " +
                    " AND p.p_identity=#{pIdentity,typeHandler = com.ocom.common.entity.typehandler.PersonInfoHandle }" +
                    " </when> " +
                    "<when test='accountState== 1' >"+
                    " AND (a.end_date  is null  or  a.end_date>=  #{date}) "+
                    "</when>" +
                    "<when test='accountState== 2' >"+
                    " AND a.end_date is not null  and  a.end_date &lt;  #{date} "+
                    "</when>" +
                    " <when test=\"tagIds != null and tagIds.size() > 0\" > " +
                    " and EXISTS (" +
                    " select tr.p_id " +
                    " from person_tag_relation tr" +
                    " left join person_tag t on tr.com_id = t.com_id and tr.tag_id = t.tag_id " +
                    " where tr.com_id = p.com_id and  tr.p_id = p.p_id and t.state = 1" +
                    " and t.tag_id in " +
                    " <foreach collection='tagIds' item='item' open='(' separator=',' close=')'> "+
                    " #{item}  "+
                    " </foreach> "+
                    " group  by  tr.p_id  having count(distinct t.group_no) >= #{groupNoCount}" +
                    " ) " +
                    " </when> " +
                    " order by p.update_time DESC" +
//                    " order by CONVERT( p.p_name USING gbk ) COLLATE gbk_chinese_ci ASC" +
                    "</script>"})
    public List<UserListRVo> GetPersonnelList(Long comId, Integer pId, Long deptId,
                                              Long parentDeptId, String pName, String pCode,
                                              String pType, String pPhone, String pIdentity, Integer status, Integer accountState, Date date, List<Integer> tagIds, int groupNoCount, String cardNo, String bankNo);


    @Select({"<script>",
            "select p.com_id as companyId,p.p_id as pId,p.dept_id as deptId,d.dept_name as deptName,\n" +
                    "p.p_name as pName,p.p_code as pCode,p.p_photo_url as pPhotoUrl,p.p_photo_url2 as pPhotoUrl2, \n" +
                    "p.p_phone as pPhone,p.p_identity as pIdentity,x.account_no as accountNo ,x.account_recharge_money as chargeBalance,x.account_subsidy_money as accountSubsidyMoney," +
                    "x.sign_type as signType,x.sign_no as signNo,x.acc_type_id as accTypeId , x.id as accountId  " +
                    " FROM personnel_info p \n" +
                    "LEFT JOIN dept_info d ON p.com_id=d.com_id AND p.dept_id=d.dept_id  " +
                    "LEFT JOIN xf_account_info x ON p.com_id=x.com_id AND p.p_id=x.p_id  " +
                    "where p.com_id=#{comId} " +
                    "  AND p.status=0" +
                    "  AND p.p_id=#{pId}" +
                    "</script>"})
    public XfUserInfoVo getXfUserInfo(Long comId, Long pId);

    @Select({"<script>", "SELECT\n" +
            " a.*, " +
            " (SELECT" +
            " b.dept_name\n" +
            " FROM\n" +
            " dept_info b\n" +
            " WHERE\n" +
            " b.dept_id = a.dept_id\n" +
            " AND b.com_id = a.com_id\n" +
            " LIMIT 1\n" +
            " ) AS dept_name\n" +
            " FROM\n" +
            " personnel_info a\n" +
            " WHERE\n" +
            " a.com_id = #{comId}" +
            " AND a.p_name = #{pName}" +
            " <when test=\"pCode!=null and pCode!=''\" > " +
            " AND a.p_code = #{pCode}" +
            " </when> " +
            " AND a.status = 0" +
            " order by a.create_time desc" +
            " LIMIT 1" +
            "</script>"})
    public UserInfoByNamePhoneVo getPinfoByNameAndCode(@Param("comId") Long comId, @Param("pName") String pName, @Param("pCode") String pCode);


    @Select({"<script>",
            " SELECT\n" +
                    " p.*, " +
                    " d.dept_name" +
                    " FROM\n" +
                    " personnel_info p\n" +
                    " LEFT JOIN card_info c ON p.com_id = c.com_id\n" +
                    " AND p.p_id = c.p_id\n" +
                    " LEFT JOIN dept_info d ON p.com_id = d.com_id\n" +
                    " AND p.dept_id = d.dept_id\n" +
                    " WHERE\n" +
                    " p.com_id = #{comId}\n" +
                    " AND p. STATUS = 0\n" +
                    " <when test=\"pId!=null and pId!=0\" > " +
                    " AND p.p_id = #{pId}\n" +
                    " </when> " +
                    " <when test=\"pCode!=null and pCode!=''\" > " +
                    " AND p.p_code = #{pCode}\n" +
                    " </when> " +
                    " <when test=\"pName!=null and pName!=''\" > " +
                    " AND p.p_name like concat('%', #{pName}, '%')\n" +
                    " </when> " +
                    " <when test=\"cardNo!=null and cardNo!=''\" > " +
                    " AND c.card_no = #{cardNo}" +
                    " </when> ",
            "</script>"})
    public List<PersonQueryVo> getPersonInfoByCardNoAndP(Long comId, Integer pId, String pCode, String pName, String cardNo);




    @Select({"<script>",
            "SELECT\n" +
                    "p.com_id," +
                    "p.p_id," +
                    "p.p_name," +
                    "p.p_code," +
                    "p.p_born," +
                    "a.id AS acc_id," +
                    "a.account_no," +
                    "a.account_state " +
                    "FROM " +
                    "personnel_info p " +
                    "INNER JOIN xf_account_info a ON a.com_id = p.com_id AND a.p_id = p.p_id  " +
                    "WHERE " +
                    "p.com_id = #{comId} " +
                    "AND a.acc_type_id = #{accType} " +
                    "AND p.status = 0 " +
                    "and  p.p_born <![CDATA[ <= ]]> #{pBorn}   \n" +
                    "limit  500",
            "</script>"})
    public List<PersonBornVo> getPersonAccountByBorn(@Param("comId")Long comId, @Param("accType")Integer accType, @Param("pBorn")String pBorn);



    @Select({"<script>",
            "SELECT p.com_id,p.p_id,p.p_photo_url2  FROM personnel_info p  LEFT JOIN personnel_face_feature f on f.com_id = p.com_id and p.p_id = f.p_id and p.p_photo_url2 = f.photo" +
                    " and f.err_code != 18 WHERE \n" +
                    "p.p_photo_url2 != '' and f.p_id is NULL   LIMIT 500 " +
                    "</script>"})
    public List<EntityPersonnelInfo> getEmptyFeaturePhoto();
}
